package com.ctbri.manage.compute.util;

import com.ctbri.manage.compute.constant.DMConstant;

/**
 * @Author wangxuem
 * @create 2023/3/8 21:02
 */
public class SqlUtil {
    public static String getNotEqualSql(String baseTable, String dataTable, int provId) {
        String style = "";
        if (baseTable.contains("mobile")){
            style = "mobile";
        }else{
            style = "station";
        }
        return "select distinct a.code,a.name, '"+style+"' as style,"+provId+" as prov_id from (select * from "+dataTable+" where prov_id="
                +provId+") a left join (select * from "+baseTable+" where prov_id="
                +provId+") b on (a.code = b.code and a.name = b.name) where b.code is null";
    }

    public static String getStaticSql(){
        //4省数据统计
//        String sql = "select d.province,d.style,coalesce(c.num, 0) as valid_num,d.num as total_num,ROUND(coalesce(c.num, 0)*100/d.num,2) as rate from (select province,style,count(distinct code)  as num from mdm.MDM_MOBILE_DATA_TMP2 group by province,style) d left join (select province, style,count(1)  as num from (select distinct a.code,a.style,a.province from mdm.MDM_MOBILE_DATA_TMP2 a inner join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code and a.province=b.province) group by province,style) c on d.province=c.province and d.style=c.style order by d.province,d.style";
        //4省未对齐数据导出
//        String sql = "select distinct a.code,a.style,a.province from mdm.MDM_MOBILE_DATA_TMP2 a left join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code and a.province=b.province where b.code is null";
        //编码和名称匹配
//        String sql = "select c.province,c.num as valid_num,d.num as total_num,ROUND(c.num*100/d.num,2) as rate from (select province,count(1)  as num from (select distinct a.code,a.name,a.province from mdm.MDM_MOBILE_DATA_TMP1 a inner join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code and a.name=b.name and a.province=b.province) group by province) c inner join (select province,count(distinct code,name)  as num from mdm.MDM_MOBILE_DATA_TMP1 group by province) d on c.province=d.province";

        //编码匹配
//        String sql = "select c.province,c.num as valid_num,d.num as total_num,ROUND(c.num*100/d.num,2) as rate from (select province,count(1)  as num from (select distinct a.code,a.name,a.province from mdm.MDM_MOBILE_DATA_TMP1 a inner join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code  and a.province=b.province) group by province) c inner join (select province,count(distinct code,name)  as num from mdm.MDM_MOBILE_DATA_TMP1 group by province) d on c.province=d.province";
//      编码不匹配的省
//        String sql = getNotEqualSqlByProvince("山东");
        //编码不匹配的所有省
        String sql = "select distinct a.name,a.code,a.province from mdm.MDM_MOBILE_DATA_TMP1 a left join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code and a.province=b.province where b.code is null order by a.province";
        //编码和名称不匹配的所有省
//        String sql = "select distinct a.name,a.code,a.province from mdm.MDM_MOBILE_DATA_TMP1 a left join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code and a.name=b.name and a.province=b.province where b.code is null order by a.province";
        return sql;
    }

    public static String getNotEqualSqlByProvince(String provinceName){
        return "select distinct a.name,a.code,a.province from (select * from mdm.MDM_MOBILE_DATA_TMP1 where province='"+
                provinceName+"') a left join (select * from mdm.MDM_MOBILE_BASE_TMP1 where province='"+
                provinceName+"') b on a.code=b.code where b.code is null";
    }

    public static String getSqlByProvince(String provinceName){
        return "select distinct a.name,a.code,a.province from mdm.MDM_MOBILE_DATA_TMP1 a left join mdm.MDM_MOBILE_BASE_TMP1 b on a.code=b.code and a.name=b.name and a.province=b.province where a.province='"+provinceName+"' and b.code is null";
    }

    public static void main(String[] args) {
        String sql = getNotEqualSql(DMConstant.TABLE_MDM_MOBILE_BASE,DMConstant.TABLE_MDM_MOBILE_DATA,832);
        System.out.println(sql);
    }
}
